import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import logging

# ---- 中文字体设置 ----
plt.rcParams['font.sans-serif'] = ['SimHei']  # 设置中文黑体
plt.rcParams['axes.unicode_minus'] = False   # 解决负号显示问题

# 日志配置
logging.basicConfig(filename='insurance_metric.log', level=logging.INFO,
                    format='%(asctime)s - %(levelname)s - %(message)s')

# 数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/test")

# 1. 读取保单数据
df = pd.read_sql("SELECT * FROM insurance_policies", engine)

# 2. 计算已赚保费
df["earned_premium"] = df["original_premium"] - df["ceded_premium"] - df["uepr"] + df["reversed_uepr"]

# 3. 各项指标计算
df["赔付率"] = round((df["claim_payment"] + df["pending_claims"] - df["reversed_pending_claims"]) / df["earned_premium"] * 100, 2)
df["综合费用率"] = round((df["admin_expense"] + df["commission"] + df["reinsurance_fee"] + df["tax"] - df["reinsurance_fee_reversal"]) / df["earned_premium"] * 100, 2)
df["综合成本率"] = round((df["claim_payment"] + df["pending_claims"] - df["reversed_pending_claims"] +
                        df["admin_expense"] + df["commission"] + df["reinsurance_fee"] + df["tax"] -
                        df["reinsurance_fee_reversal"]) / df["earned_premium"] * 100, 2)
df["保费费用率"] = round(df["admin_expense"] / df["original_premium"] * 100, 2)
df["手续费及佣金比率"] = round(df["commission"] / df["original_premium"] * 100, 2)
df["分保费用比率"] = round(df["reinsurance_fee"] / df["ceded_premium"] * 100, 2)

# 4. 输出平均值
mean_metrics = df[["赔付率", "综合费用率", "综合成本率", "保费费用率", "手续费及佣金比率", "分保费用比率"]].mean()
print("📊 业务指标平均值：\n", mean_metrics)
logging.info("✅ 业务指标计算完成，平均值如下：\n%s", mean_metrics.to_string())

# 5. 写入数据库各指标表
metrics = {
    "claim_ratio": "赔付率",
    "expense_ratio": "综合费用率",
    "cost_ratio": "综合成本率",
    "premium_expense_ratio": "保费费用率",
    "commission_ratio": "手续费及佣金比率",
    "ceded_expense_ratio": "分保费用比率"
}

for table_name, column_name in metrics.items():
    metric_df = df[["policy_id", column_name]].copy()
    metric_df.columns = ["policy_id", "value"]
    try:
        metric_df.to_sql(table_name, con=engine, if_exists='append', index=False)
        logging.info(f"✅ 成功写入表 {table_name}，共{len(metric_df)}条记录")
        print(f"✅ 写入表 {table_name} 成功")
    except Exception as e:
        logging.error(f"❌ 写入表 {table_name} 失败：{str(e)}")
        print(f"❌ 写入表 {table_name} 失败")

# 6. 可视化展示多个图表
指标列表 = list(metrics.values())

plt.figure(figsize=(18, 12))

# 6-1 各指标直方图
for i, 指标 in enumerate(指标列表, 1):
    plt.subplot(3, 3, i)
    plt.hist(df[指标].dropna(), bins=30, color='lightblue', edgecolor='black')
    plt.title(f"{指标} 分布")
    plt.xlabel("百分比 (%)")
    plt.ylabel("保单数量")
    plt.grid(True)

# 6-2 各指标箱线图（第7子图）
plt.subplot(3, 3, 7)
df[指标列表].boxplot()
plt.title("指标箱线图")
plt.ylabel("百分比 (%)")
plt.xticks(rotation=45)

# 6-3 各指标平均值柱状图（第8子图）
plt.subplot(3, 3, 8)
plt.bar(mean_metrics.index, mean_metrics.values, color='skyblue',alpha=0.7)
plt.title("各指标平均值")
plt.ylabel("平均百分比 (%)")
plt.xticks(rotation=45)
plt.grid(True)

plt.tight_layout()
plt.show()
